package zy.dao.wx.my.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.wx.my.WxMyDAO;
import zy.entity.sell.ecoupon.T_Sell_Ecoupon_User;
import zy.entity.wx.my.T_Wx_Address;

@Repository
public class WxMyDAOImpl extends BaseDaoImpl implements WxMyDAO {

	@Override
	public Map<String, Object> getMyValue(Map<String, Object> params) {
		Map<String, Object> myValue = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("");

		sql.append("SELECT wu_balance AS balance FROM t_wx_user t WHERE 1=1 ");
		sql.append(" AND t.wu_mobile = :mobile ");
		sql.append(" AND t.wu_state = 0 ");//正常
		Double balance = 0.0d;
		try {
			balance = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class);
			myValue.put("balance", balance);//账户余额
		} catch (Exception e) {
			myValue.put("balance", 0.0d);
		}
		
		sql.setLength(0);
		sql.append("SELECT COUNT(1) As ecouponCount FROM t_sell_ecoupon_user ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND companyid = :companyid ");
		sql.append(" AND ecu_shop_code = :shop_code ");
		sql.append(" AND ecu_tel = :mobile ");
		sql.append(" AND ecu_state = 0 ");//未使用
		Integer ecouponCount = 0;
		try {
			ecouponCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
			myValue.put("ecouponCount", ecouponCount);//优惠券
		} catch (Exception e) {
			myValue.put("ecouponCount", 0);
		}
		
		sql.setLength(0);
		sql.append("SELECT SUM(t.cd_money) AS cd_money FROM t_sell_card t WHERE 1=1 ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" AND t.cd_shop_code = :shop_code ");
		sql.append(" AND t.cd_mobile = :mobile ");
		sql.append(" AND t.cd_state = 0 ");//正常
		sql.append(" GROUP BY t.cd_mobile ");
		Double cardValue = 0.0d;
		try {
			cardValue = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class);
			myValue.put("cardValue", cardValue);//储值卡
		} catch (Exception e) {
			myValue.put("cardValue", 0.0d);
		}
		
		sql.setLength(0);
		sql.append("SELECT COUNT(1) FROM t_sell_voucher t WHERE 1=1 ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" AND t.vc_shop_code = :shop_code ");
		sql.append(" AND t.vc_mobile = :mobile ");
		sql.append(" AND t.vc_state = 0 ");//正常
		Integer voucherCount = 0;
		try {
			voucherCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
			myValue.put("voucherCount", voucherCount);//代金券
		} catch (Exception e) {
			myValue.put("voucherCount", 0);
		}
		
		
		sql.setLength(0);
		sql.append("SELECT vm_points FROM t_vip_member t WHERE 1=1 ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" AND t.vm_mobile = :mobile ");
		sql.append(" AND t.vm_state = 0 ");//正常
		Double points = 0.0d;
		try {
			points = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class);
			myValue.put("points", points);//积分
		} catch (Exception e) {
			myValue.put("points", 0.0d);//积分
		}
		return myValue;
	}

	@Override
	public String getNowPasswordByWuCode(String wu_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wu_password FROM t_wx_user WHERE 1=1 AND wu_code = :wu_code ");
		String wu_password = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
										new MapSqlParameterSource().addValue("wu_code", wu_code), String.class);
		return wu_password;
	}

	@Override
	public void updatePassword(String wu_code, String wp_password) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_wx_user ");
		sql.append(" SET wu_password = :wp_password");
		sql.append(" WHERE wu_code=:wu_code");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wu_code", wu_code).addValue("wp_password", wp_password));
	}

	@Override
	public List<T_Wx_Address> getAddrListByUserCode(String wu_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wd_id,wd_code,wd_name,wd_mobile,wd_province,wd_city,");
		sql.append(" wd_area,wd_town,wd_addr,wd_zipcode,wd_state");
		sql.append(" FROM t_wx_address ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wd_user_code = :wu_code ");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("wu_code", wu_code), new BeanPropertyRowMapper<>(T_Wx_Address.class));
	}

	@Override
	public void updateAddressStateByUsCode(String wu_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_wx_address ");
		sql.append(" SET wd_state = 0 ");
		sql.append(" WHERE wd_user_code=:wu_code");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wu_code", wu_code));
	}

	@Override
	public T_Wx_Address saveAddress(T_Wx_Address t_Wx_Address) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(wd_code+0)) FROM t_wx_address ");
		String wd_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(t_Wx_Address), String.class);
		t_Wx_Address.setWd_code(wd_code);
		sql.setLength(0);
		sql.append("INSERT INTO t_wx_address");
		sql.append(" (wd_code,wd_user_code,wd_name,wd_mobile,wd_province,wd_city,wd_area,wd_town,wd_addr,wd_zipcode,wd_state,wd_sysdate,wd_x,wd_y)");
		sql.append(" VALUES ");
		sql.append("(:wd_code,:wd_user_code,:wd_name,:wd_mobile,:wd_province,:wd_city,:wd_area,:wd_town,:wd_addr,:wd_zipcode,:wd_state,:wd_sysdate,:wd_x,:wd_y)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_Wx_Address),holder);
		t_Wx_Address.setWd_id(holder.getKey().intValue());
		return t_Wx_Address;
	}

	@Override
	public void updateAddressStateById(Integer wd_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_wx_address ");
		sql.append(" SET wd_state = 1 ");
		sql.append(" WHERE wd_id=:wd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wd_id", wd_id));
	}

	@Override
	public T_Wx_Address getAddressInfoById(Integer wd_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wd_id,wd_code,wd_name,wd_mobile,wd_province,wd_city,");
		sql.append(" wd_area,wd_town,wd_addr,wd_zipcode,wd_state");
		sql.append(" FROM t_wx_address ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wd_id = :wd_id ");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("wd_id", wd_id), 
				new BeanPropertyRowMapper<>(T_Wx_Address.class));
	}
	
	@Override
	public T_Wx_Address getDefaultAddressInfoByCode(String wd_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT wd_id,wd_code,wd_name,wd_mobile,wd_province,wd_city,");
		sql.append(" wd_area,wd_town,wd_addr,wd_zipcode,wd_state");
		sql.append(" FROM t_wx_address ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wd_code = :wd_code ");
		sql.append(" AND wd_state = 1 ");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("wd_code", wd_code), 
				new BeanPropertyRowMapper<>(T_Wx_Address.class));
	}
	

	@Override
	public void deleteAddressById(Integer wd_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_wx_address ");
		sql.append(" WHERE wd_id=:wd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wd_id", wd_id));
	}

	@Override
	public void updateAddressById(T_Wx_Address t_Wx_Address) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_wx_address SET ");
		sql.append(" wd_name=:wd_name,wd_mobile=:wd_mobile,wd_province=:wd_province,wd_city=:wd_city,wd_area=:wd_area,wd_town=:wd_town,");
		sql.append(" wd_addr=:wd_addr,wd_zipcode=:wd_zipcode,wd_state=:wd_state,wd_x=:wd_x,wd_y=:wd_y ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND wd_id=:wd_id ");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_Wx_Address));
	}

	@Override
	public List<T_Sell_Ecoupon_User> getMyCouponList(Map<String, Object> params) {

		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecu_id,ecu_ec_number,ecu_code,ecu_money,ecu_limitmoney,ecu_begindate,ecu_enddate ");
		sql.append(" FROM t_sell_ecoupon_user ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND companyid = :companyid ");
		sql.append(" AND ecu_shop_code = :shop_code ");
		sql.append(" AND ecu_tel = :wu_mobile ");
		sql.append(" AND ecu_state = 0 ");//未使用
		//sql.append(" AND ecu_use_type = 2 ");//微商城
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Ecoupon_User.class));
	}
}
